可能会|末尾_为什么我建议在复杂但是性能关键的表上所有查询都加上forceindex

篇首语：本文由编程笔记#小编为大家整理，主要介绍了为什么我建议在复杂但是性能关键的表上所有查询都加上 force index相关的知识，希望对你有一定的参考价值。

最近&＃xff0c;又遇到了慢 SQL&＃xff0c;简单的看了下&＃xff0c;又是因为 mysql 本身优化器还有查询计划估计不准的问题。SQL 如下&＃xff1a;

select * from t_pay_record
WHERE
((
user_id &＃61; &＃39;user_id1&＃39;
AND is_del &＃61; 0
))
ORDER BY
id DESC
LIMIT 20


这个 SQL 执行了 20 分钟才有结果。但是我们换一个 user_id&＃xff0c;执行就很快。从线上业务表现来看&＃xff0c;大部分用户的表现都正常。我们又用一个数据分布与这个用户相似的用户去查&＃xff0c;还是比较快。

我们先来 EXPLAIN 下这个原始 SQL&＃xff0c;结果是&＃xff1a;

&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;---------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;---------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;
| 1 | SIMPLE | t_pay_record | NULL | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | PRIMARY | 8 | NULL | 22593 | 0.01 | Using where |
&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;---------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;


然后我们换一些分布差不多的用户但是响应时间正常的用户&＃xff0c;EXPLAIN 结果有的是&＃xff1a;

&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;---------------------------------------------------------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;---------------------------------------------------------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;
| 1 | SIMPLE | t_pay_record | NULL | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id_trade_code_status_amount_create_time_is_del | 195 | NULL | 107561| 10.00| Using where |
&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;---------------------------------------------------------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;


有的是&＃xff1a;

&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;-------------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;-------------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;
| 1 | SIMPLE | t_pay_record | NULL | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id | 195 | NULL | 87514| 10.00| Using where |
&＃43;----&＃43;-------------&＃43;--------------&＃43;------------&＃43;-------&＃43;-----------------------------------------------------------------------------------------&＃43;-------------&＃43;---------&＃43;------&＃43;-------&＃43;----------&＃43;-------------&＃43;


其实根据这个表现就可以推断出&＃xff0c;是走错索引了。为啥会用错索引呢&＃xff1f;这个是因为多方面的原因导致的&＃xff0c;本篇文章将针对这个 SQL 来分析下这个多方面的原因&＃xff0c;并给出最后的解决办法。

对于 MySQL 慢 SQL 的分析

在之前的文章&＃xff0c;我提到过 SQL 调优一般通过下面三个工具&＃xff1a;

1. EXPLAIN&＃xff1a;这个是比较浅显的分析&＃xff0c;并不会真正执行 SQL&＃xff0c;分析出来的可能不够准确详细。但是能发现一些关键问题。
2. PROFILING&＃xff1a; 通过 set profiling &＃61; 1 开启的 SQL 执行采样。可以分析 SQL 执行分为哪些阶段&＃xff0c;并且每阶段的耗时如何。需要执行并且执行成功 SQL&＃xff0c;并且分析出来的阶段不够详细&＃xff0c;一般只能通过某些阶段是否存在如何避免这些阶段的出现进行优化&＃xff08;例如避免内存排序的出现等等&＃xff09;。
3. OPTIMIZER TRACE&＃xff1a;详细展示优化器的每一步&＃xff0c;需要执行并且执行成功 SQL。MySQL 的优化器由于考虑的因素太多&＃xff0c;迭代太多&＃xff0c;配置相当复杂&＃xff0c;默认的配置在大部分情况没问题&＃xff0c;但是在某些特殊情况会有问题&＃xff0c;需要我们进行人为干预。

这里再说一下在不同的 MySQL 版本&＃xff0c; EXPLAIN 和 OPTIMIZER TRACE 结果可能不同&＃xff0c;这是 MySQL 本身设计上的不足导致的&＃xff0c;EXPLAIN 更贴近最后的执行结果&＃xff0c;OPTIMIZER TRACE 相当于在每一步埋点采集&＃xff0c;在 MySQL 不断迭代开发的时候&＃xff0c;难免会有疏漏

对于上面这个 SQL&＃xff0c;我们其实 EXPLAIN 就能知道它的原因是走错索引了。但是不能直观的看出来为啥会走错索引&＃xff0c;需要通过 OPTIMIZER TRACE 进行进一步定位。但是在进一步定位之前&＃xff0c;我想先说一下 MySQL 的 InnoDB 查询优化器数据配置。

MySQL InnoDB 查询优化器数据配置&＃xff08;MySQL InnoDB Optimizer Statistics&＃xff09;

官网文档地址&＃xff1a;https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

为了优化用户的 SQL 查询&＃xff0c;MySQL 会对所有 SQL 查询进行 SQL 解析、改写和查询计划优化。针对 InnoDB 引擎&＃xff0c;制定查询计划的时候要分析&＃xff1a;

1. 全表扫描消耗是多大
2. 走索引可以走哪些索引&＃xff1f;会考虑 where 条件&＃xff0c;以及 order 条件&＃xff0c;通过里面的条件找有这些条件的索引
3. 每个索引的查询消耗是多大
4. 选出消耗最小的那个查询计划并执行

每个索引查询消耗&＃xff0c;需要通过 InnoDB 查询优化器数据。这个数据是通过采集表以及索引数据得出的&＃xff0c;并且并不是全量采集&＃xff0c;而是抽样采集。与以下配置相关&＃xff1a;

1. innodb_stats_persistent 全局变量控制全局默认的数据是否持久化&＃xff0c;默认为 ON 即持久化&＃xff0c;我们一般不会能接受在内存中保存&＃xff0c;这样万一数据库重启&＃xff0c;表就要重新分析&＃xff0c;这样减慢启动时间。控制单个表的配置是 STATS_PERSISTENT&＃xff08;在 CREATE TABLE 以及 ALTER TABLE 中使用&＃xff09;。
2. innodb_stats_auto_recalc 全局变量全局默认是否自动更新&＃xff0c;默认为 ON 即在表中有 10% 以上的行更新后触发后台异步更新采集数据&＃xff0c;。控制单个表的配置是 STATS_AUTO_RECALC&＃xff08;在 CREATE TABLE 以及 ALTER TABLE 中使用&＃xff09;。
3. innodb_stats_persistent_sample_pages 全局变量控制全局默认的采集页的数量&＃xff0c;默认为 20. 即每次更新&＃xff0c;随机采集表以及表中的每个索引的 20 页数据&＃xff0c;用于估算每个索引的查询消耗是多大以及全表扫描消耗是多大&＃xff0c;控制单个表的配置是 STATS_SAMPLE_PAGES&＃xff08;在 CREATE TABLE 以及 ALTER TABLE 中使用&＃xff09;。

执行时间最慢的 SQL 原因定位

通过之前的 EXPLAIN 的结果&＃xff0c;我们知道最后的查询用的索引是 PRIMARY 主键索引&＃xff0c;这样的话整个 SQL 的执行过程就是&＃xff1a;通过主键倒序遍历表中的每一条数据&＃xff0c;直到筛选出 20 条。通过执行耗时我们知道&＃xff0c;这个遍历了很多数据才凑满 20 条&＃xff0c;效率极其低下。为啥会这样呢&＃xff1f;

通过 SQL 语句我们知道&＃xff0c;在前面提到的第二步中&＃xff0c;考虑的索引包括 where 条件中的 user_id&＃xff0c;is_del 相关的索引&＃xff08;通过 EXPLAIN 我们知道有这些索引&＃xff1a;idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del&＃xff09;&＃xff0c;以及 order by 条件中的 id 索引&＃xff0c;也就是主键索引。假设本次随机采集中采集的页数据是这个样子的&＃xff1a;

图中蓝色的代表抽样到的页&＃xff0c;同一个表内每个索引都会抽样默认 20 页。假设本次采集的结果就是图中所示&＃xff0c;其他索引采集的比较均衡&＃xff0c;通过其他索引判断用户都要扫描几万行的结果。但是主键采集的最后一页&＃xff0c;正好末尾全是这个用户的记录。由于语句最后有 limit 20&＃xff0c;如果末尾正好有 20 条记录&＃xff08;并且都符合 where 条件&＃xff09;&＃xff0c;那么就会认为按照主键倒着找 20 条记录就可以了。这样就会造成优化器认为走主键扫描消耗最少。但是实际上并不是这样&＃xff0c;因为这是采样的&＃xff0c;没准后面有很多很多不是这个用户的记录&＃xff0c;对大表尤其如此。

如果我们把 limit 去掉&＃xff0c;EXPLAIN 就会发现索引走对了&＃xff0c;因为不限制 limit&＃xff0c;主键索引就要全部扫描一遍&＃xff0c;消耗怎么也不可能比 user_id 相关的索引低了。

执行时间正常的 SQL 为啥 user_id 不同也会走分析出走不同索引的原因

同样的&＃xff0c;由于所有索引的优化器数据是随机采样的&＃xff0c;随着表的不断变大以及索引的不断膨胀&＃xff0c;还有就是可能加更复杂的索引&＃xff0c;这样会加剧使用不同参数分析索引消耗的差异性&＃xff08;这里就是使用不同的 user_id&＃xff09;。

这也引出了一个新的可能大家也会遇到的问题&＃xff0c;我在原有索引的基础上&＃xff0c;加了一个复合索引&＃xff08;举个例子就是原来只有 idx_user_id&＃xff0c;后来加了 idx_user_status_pay&＃xff09;&＃xff0c;那么原来的只按照 user_id 去查数据的 SQL&＃xff0c;有的可能会使用
idx_user_id&＃xff0c;有的可能会使用 idx_user_status_pay&＃xff0c;使用 idx_user_status_pay 大概率比使用 idx_user_id&＃xff0c; 慢。所以&＃xff0c;添加新的复合索引&＃xff0c;可能会导致原来的不是这个复合索引要优化的 SQL 的其他业务 SQL 变慢&＃xff0c;所以需要慎重添加

这种设计&＃xff0c;在数据量不断增大表越变越复杂的时候&＃xff0c;会带来哪些问题

1. 由于统计数据不是实时更新&＃xff0c;而是更新的行数超过一定比例才会开始更新。并且统计数据不是全量统计&＃xff0c;是抽样统计。所以在表的数据量很大的时候&＃xff0c;这个统计数据很难非常准确。
2. 由于统计数据本来就不够准确&＃xff0c;表设计如果也比较复杂&＃xff0c;存储的数据类型比较多&＃xff0c;字段也很多&＃xff0c;并且最关键的是有各种复合索引&＃xff0c;索引也越来越复杂&＃xff0c;这样更加加剧了这个统计数据的不准确性。
3. 顺便说一下&＃xff1a;MySQL 表数据量不能很大&＃xff0c;需要做好水平拆分&＃xff0c;同时字段不能太多&＃xff0c;所以需要做好垂直拆分。并且索引不能随便加&＃xff0c;想加多少加多少&＃xff0c;也有以上说的这两个原因&＃xff0c;这样会加剧统计数据的不准确性&＃xff0c;导致用错索引。
4. 手动 Analyze Table&＃xff0c;会在表上加读锁&＃xff0c;会阻塞表上的更新以及事务。所以不能在这种在线业务关键表上面使用。可以考虑在业务低峰的时候&＃xff0c;定时 Analyze 业务关键 Table
5. 依靠表本身自动刷新数据机制&＃xff0c;参数比较难以调整&＃xff08;主要是 STATS_SAMPLE_PAGES 这个参数&＃xff0c;STATS_PERSISTENT 我们一般不会改&＃xff0c;我们不会能接受在内存中保存&＃xff0c;这样万一数据库重启&＃xff0c;表就要重新分析&＃xff0c;这样减慢启动时间&＃xff0c;STATS_AUTO_RECALC 我们也不会关闭&＃xff0c;这样会导致优化器分析的越来越不准确&＃xff09;&＃xff0c;很难预测出到底调整到什么数值最合适。并且业务的增长&＃xff0c;用户的行为导致的数据的倾斜&＃xff0c;也是很难预测的。通过 Alter Table 修改某个表的 STATS_SAMPLE_PAGES 的时候&＃xff0c;会导致和 Analyze 这个 Table 一样的效果&＃xff0c;会在表上加读锁&＃xff0c;会阻塞表上的更新以及事务。所以不能在这种在线业务关键表上面使用。所以最好一开始就能估计出大表的量级&＃xff0c;但是这个很难。

结论和建议

综上所述&＃xff0c;我建议线上对于数据量比较大的表&＃xff0c;最好能提前通过分库分表控制每个表的数据量&＃xff0c;但是业务增长与产品需求都是不断在迭代并且变复杂的。很难保证不会出现大并且索引比较复杂的表。这种情况下需要我们&＃xff0c;在适当调高 STATS_SAMPLE_PAGES 的前提下&＃xff0c;对于一些用户触发的关键查询 SQL&＃xff0c;使用 force index 引导它走正确的索引&＃xff0c;这样就不会出现本文中说的因为 MySQL 优化器表采集数据的不准确导致的某些用户 id 查询走错索引的情况。

微信搜索“我的编程喵”关注公众号&＃xff0c;加作者微信&＃xff0c;每日一刷&＃xff0c;轻松提升技术&＃xff0c;斩获各种offer&＃xff1a;

我会经常发一些很好的各种框架的官方社区的新闻视频资料并加上个人翻译字幕到如下地址&＃xff08;也包括上面的公众号&＃xff09;&＃xff0c;欢迎关注&＃xff1a;

• 知乎&＃xff1a;https://www.zhihu.com/people/zhxhash
• B 站&＃xff1a;https://space.bilibili.com/31359187